import sqlite3
import os

class Database:
    def __init__(self, db_name='dormitory.db'):
        # 确保数据库文件在项目根目录
        self.db_path = os.path.join(os.path.dirname(os.path.abspath(__file__)), db_name)
        self.conn = None
        self.cursor = None
        
    def connect(self):
        """连接到数据库"""
        self.conn = sqlite3.connect(self.db_path)
        self.cursor = self.conn.cursor()
        return self.conn
    
    def close(self):
        """关闭数据库连接"""
        if self.conn:
            self.conn.close()
            
    def init_db(self):
        """初始化数据库表结构"""
        self.connect()
        
        # 创建宿舍表
        self.cursor.execute('''
        CREATE TABLE IF NOT EXISTS dormitory (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            building TEXT NOT NULL,
            room_number TEXT NOT NULL,
            capacity INTEGER NOT NULL,
            current_count INTEGER DEFAULT 0,
            status TEXT DEFAULT 'available',
            UNIQUE(building, room_number)
        )
        ''')
        
        # 创建学生表
        self.cursor.execute('''
        CREATE TABLE IF NOT EXISTS student (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            student_id TEXT UNIQUE NOT NULL,
            name TEXT NOT NULL,
            gender TEXT NOT NULL,
            class_name TEXT NOT NULL,
            contact TEXT,
            dormitory_id INTEGER,
            FOREIGN KEY (dormitory_id) REFERENCES dormitory(id)
        )
        ''')
        
        # 创建宿舍分配表
        self.cursor.execute('''
        CREATE TABLE IF NOT EXISTS allocation (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            student_id INTEGER NOT NULL,
            dormitory_id INTEGER NOT NULL,
            allocation_date TEXT NOT NULL,
            status TEXT DEFAULT 'active',
            FOREIGN KEY (student_id) REFERENCES student(id),
            FOREIGN KEY (dormitory_id) REFERENCES dormitory(id)
        )
        ''')
        
        # 创建维修记录表
        self.cursor.execute('''
        CREATE TABLE IF NOT EXISTS maintenance (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            dormitory_id INTEGER NOT NULL,
            issue_description TEXT NOT NULL,
            report_date TEXT NOT NULL,
            status TEXT DEFAULT 'pending',
            resolved_date TEXT,
            FOREIGN KEY (dormitory_id) REFERENCES dormitory(id)
        )
        ''')
        
        # 创建用户表
        self.cursor.execute('''
        CREATE TABLE IF NOT EXISTS user (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            username TEXT UNIQUE NOT NULL,
            password TEXT NOT NULL,
            role TEXT NOT NULL,
            name TEXT,
            contact TEXT,
            status TEXT DEFAULT 'active'
        )
        ''')
        
        # 创建费用表
        self.cursor.execute('''
        CREATE TABLE IF NOT EXISTS fee (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            dormitory_id INTEGER NOT NULL,
            fee_type TEXT NOT NULL,
            amount REAL NOT NULL,
            billing_date TEXT NOT NULL,
            due_date TEXT NOT NULL,
            payment_status TEXT DEFAULT 'unpaid',
            payment_date TEXT,
            FOREIGN KEY (dormitory_id) REFERENCES dormitory(id)
        )
        ''')
        
        # 提交更改
        self.conn.commit()
        self.close()
        
# 如果直接运行此文件，则初始化数据库
if __name__ == '__main__':
    db = Database()
    db.init_db()
    print("数据库初始化完成")